<?php

/**
 * @author 孙春雷
 */
class Controller_Warehouse extends Controller_Admin
{

    /**
     *手动新建入库单
     */
    public function before()
    {
        try{
            ACL::Required('add warehouse');
        }catch(HTTP_Exception_403 $e){
            Message::error('sorry，你没有权限进这个目录');
            $this->request->redirect(URL::base(TRUE));
        }
        parent::before();
    }

    // 手工入库界面
    public function action_special() {
        $this->title = '新建入库单-手动录入';
        $status = delivery::get_delivery_status('warehouseorders','type');
        //$warehouse   = delivery::get_warehouse_list();
        $warehouse = Kohana::$config->load('warehouse')->warehouses;
        $enterprise_dominants = Kohana::$config->load('enterprise_dominant')->as_array();
        $view = View::factory('warehouse/special')->set('warehouse', $warehouse)
            ->set('status',$status)->set('enterprise_dominants', $enterprise_dominants);
        $this->response->body($view);
    }
    /*暂时没用了
        // 扫描入库
        public function action_specialsaomiao()
        {
            $this->title = '新建入库单-退件处理';
            $view = View::factory( 'warehouse/tuijian' );
            $this->response->body( $view );
        }

        // 查询退件单号数据
        public function action_select_tuijian()
        {
            $this->title = '新建入库单-退件处理';
            $results=array();
            if(isset($_GET['erp_order_id'])){
              $erp_order=Warehouse::get_order($_GET['erp_order_id']);
                if($erp_order['erp_op_id']!=2&&$erp_order['erp_op_id']!=8)
                {
                        Message::error("你输入的订单号在老erp还没有退回记录");
                }else {
                    $results=Warehouse::get_product($_GET['erp_order_id']);
                }
                for ($i=0;$i<count($results);$i++){
                    $sku_info=Basic::load_sku_info($results[$i]['sku']);
                    $results[$i]['products_place']=$sku_info['products_place'];
                    $results[$i]['products_name_cn']=$sku_info['products_name_cn'];
                    $results[$i]['size']=$sku_info['size'];
                    $results[$i]['status']=Warehouse::in_status($results[$i]['orders_sku'],$results[$i]['erp_orders_id']);
                }
            }
            $orderid = $_GET['erp_order_id'];
            $view = View::factory( 'warehouse/tuijian' )
            ->bind('results', $results);
            $this->response->body( $view );
        }
        */
    // 手工入库添加到数据库
    public function action_specialadd()
    {
        //$warehouse_id = Kohana::$config->get('warehouse')->warehouse_id;

        $warehouse        = $_POST;
        $warehouse['sku'] = strtoupper(trim($warehouse['sku']));
        unset($warehouse ['supplier_name']);
        $warehouse ['op_time']       = date('Y-m-d H:i:s');
        $warehouse['warehouse_date'] = date('Y-m-d H:i:s');
        $warehouse ['warehouse_man'] = User::active_user()->id;
        //$warehouse ['warehouseid']   = $warehouse['warehouseid'];

        // $res = hznewerpclient::getNewPrice(array($warehouse['sku']));
        $res = $this->getSignlePrice(array($warehouse['sku']), $warehouse['supplier_id']);
        $warehouse ['single_price']   = $res[$warehouse['sku']];
        $warehouse ['money']    = $warehouse ['single_price']*$warehouse['warehouse_quantity'];
        $warehouse ['cost']     = $warehouse ['single_price'] * $warehouse['warehouse_quantity'] + $warehouse['transportation_expense'];
        //yuqing --end--
        $model = new Model_Warehouseorders();
        if(Basic::checktime($warehouse['warehouse_date'])){
            $jsonarr = array(
                'status' => 'n',
                'info'   => '关账期间不能新建入库，请找财务'
            );
            echo json_encode($jsonarr);
            exit();
        }
        list($riid,$count) = $model->Add($warehouse);
        if($count){
            $jsonarr   = array(
                'status' => 'y',
                'info'   => '添加成功',
                'id'     => $riid
            );
            $date      = date('Y-m',time());
            $date1     = $date.'-01';
            $inventory = ORM::factory('inventory')
                ->where('sku','=',$warehouse['sku'])
                ->where('warehouseid', '=', $warehouse['warehouseid'])
                ->where('inventory_date','=',$date1)
                ->find();
            if($inventory->id==''){

                $inventory_in                 = ORM::factory('inventory');
                $inventory_in->inventory_date = $date1;
                $inventory_in->sku            = strtoupper($warehouse['sku']);
                $inventory_in->sku_name       = $warehouse['sku_name'];
                //$inventory_in->sku_standard = $warehouse['sku_standard'];
                $inventory_in->storage_position = $warehouse['storage_position'];
                $inventory_in->supplier_id      = $warehouse['supplier_id'];
                $inventory_in->quantity         = 0;
                $inventory_in->cost             = 0;
                $inventory_in->warehouseid      = $warehouse['warehouseid'];
                $inventory_in->save();
            }
        }else{
            $jsonarr = array(
                'status' => 'n',
                'info'   => '添加失败'
            );
        }
        echo json_encode($jsonarr);
        exit;
    }

    /**
     * 根据sku返回供应商和供应商产品信息，json数据
     */
    public function action_get_supplier_by_sku(){
        $sku      = $_GET ['sku'];
        $supplier = new Service_Common();
        $result   = json_encode($supplier->get_supplier_by_sku_new($sku));
        echo $result;
        exit;
    }

    /**
     * 获取临时出库的出库单的单价金额和成本
     */
    public function action_get_do_cost()
    {
        $do_id = $_GET['do_id'];
        $do    = ORM::factory('deliveryorder')
            ->where('id','=',$do_id)
            ->where('sku','=',$_GET['sku'])
            ->find();
        $re    = array(
            'sku'          => '',
            'single_price' => 0,
            'money'        => 0,
            'cost'         => 0,
            'num'          => 1
        );
        if($do->id!=''){
            $re['sku']          = $_GET['sku'];
            $re['single_price'] = $do->single_price;
            $re['money']        = $do->money;
            $re['cost']         = $do->cost;
            $re['num']          = $do->quantity;
        }
        echo json_encode($re);
    }

    /**
     * 根据sku和供应商得到供应商产品的信息，json数据
     * @return string
     */
    public function action_get_sku_price()
    {
        $sku = $_POST ['sku'];
        $supplier_id = $_POST['supplier_id'];

        $supplier = new Service_Common();
        $result   = json_encode($supplier->get_sku_price($sku, $supplier_id));
        echo $result;
    }

    /**
     * 下载默认表格
     */
    public function action_default_down()
    {
        $this->title = '下载模版';
        require_once(Kohana::find_file('vendor','PHPExcel/PHPExcel/IOFactory'));
        $this->objReader = PHPExcel_IOFactory::createReader('Excel5');
        $objPHPExcel     = new PHPExcel();
        $objPHPExcel->getProperties()
            ->setCreator("yksvmi")
            ->setLastModifiedBy("yksvmi")
            ->setTitle("Office 2003 XLSX Test Document")
            ->setSubject("Office 2003 XLSX Test Document")
            ->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
            ->setKeywords("office 2003 openxml php")
            ->setCategory("Test result file");
        $objActSheet = $objPHPExcel->setActiveSheetIndex(0);
        $objActSheet->setCellValue('A1','订单号（erp内单号）');
        $objActSheet->setCellValue('B1','SKU');
        $objActSheet->setCellValue('C1','数量');
        $objPHPExcel->getActiveSheet()
            ->setTitle('warehouse_special');
        $objPHPExcel->setActiveSheetIndex(0);
        $day = date("m-d");
        ob_end_clean();
        header("Content-Type: application/vnd.ms-excel; charset=utf-8");
        header('Content-Disposition: attachment;filename="'.$day.'特殊入库单.xls"');
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    /**暂停使用
     * 上传订单退件，入库
     * public function action_upload_warehouse()
     * {
     * $this->title='批量上传入库';
     * set_time_limit(0);
     * ini_set('memory_limit','500M');
     * require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
     * $xls= $_FILES['xls']['tmp_name'];
     * $objPHPExcel= PHPExcel_IOFactory::load($xls);
     * $data= $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
     * $info = '表格内容为空';
     * $num = count($data);
     * for($i=2;$i<=$num;$i++){
     * //判断表格是否为空
     * if(!isset($data[$i]['A']) || $data[$i]['A']==''){
     * $info = '第'.$i.'行第A列数据为空';break;
     * }else if(!isset($data[$i]['B']) || $data[$i]['B']==''){
     * $info = '第'.$i.'行第B列数据为空';break;
     * }else if(!isset($data[$i]['C']) || $data[$i]['C']==''){
     * $info = '第'.$i.'行第C列数据为空';break;
     * }
     * //判断此订单退件是否已入库
     * $wo = ORM::factory('warehouseorder')->where('remark','=',trim($data[$i]['A']))->where('sku','=',trim($data[$i]['B']))->where('type','=','30')->find();
     * if($wo->id!=''){
     * $info = '第'.$i."行的".$data[$i]['A']."这个订单号的".$data[$i]['B']."这个sku已入库";
     * break;
     * }
     * //判断此订单退件是否有出库
     * $do = ORM::factory('deliveryorder')->where('order_id','=',trim($data[$i]['A']))->where('sku','=',trim($data[$i]['B']))->find();
     * if($do->id==''){
     * $info = '第'.$i."行的".$data[$i]['A']."这个订单号的".$data[$i]['B']."这个sku在出库单表中没有此记录";
     * break;
     * }else if($do->quantity<trim($data[$i]['C'])){
     * $info = '第'.$i.'行的'.$data[$i]['A']."这个订单号的退件入库数大于原本出库单的出库数量，请修正";
     * break;
     * }else{
     * $info = 'yes';
     * }
     * }
     * if($info=='yes'){
     * for($i=2;$i<=$num;$i++){
     * $delivery_order = ORM::factory('deliveryorder')->where('order_id','=',trim($data[$i]['A']))->where('sku','=',trim($data[$i]['B']))->find();
     * $wo = ORM::factory('warehouseorder');
     * $wo->warehouse_date = date('Y-m-d H:i:s');
     * $wo->sku = $delivery_order->sku;
     * $wo->sku_name = $delivery_order->sku_name;
     * $wo->sku_standard = $delivery_order->sku_standard;
     * $wo->storage_position = $delivery_order->storage_position;
     * $wo->warehouse_quantity = trim($data[$i]['C']);
     * $wo->single_price = $delivery_order->single_price;
     * $wo->money  =  $delivery_order->single_price*trim($data[$i]['C']);
     * $wo->cost =  $delivery_order->cost;
     * $wo->remark = $delivery_order->order_id;
     * $wo->type = 30;
     * $wo->warehouse_man = User::active_user()->id;
     * $wo->save();
     * }
     * $info= "ok";
     * }
     * if($info=='ok'){
     * Message::success('恭喜，此次退件入库成功');
     * }else{
     * Message::error($info);
     * }
     * $content=View::factory("warehouse/special");
     * $this->response->body($content);
     * }
     */
    public function  action_tuijian_insert()
    {
        $condition = $_GET;
        $flag      = Warehouse::tuijian_insert($condition);
        echo $flag;
    }

    /*
     * @author 俞晴
     *  下载入库单模板
     */
    public function action_down_template()
    {
        $this->title = '下载模版';
        require_once(Kohana::find_file('vendor','PHPExcel/PHPExcel/IOFactory'));
        $this->objReader = PHPExcel_IOFactory::createReader('Excel5');
        $objPHPExcel     = new PHPExcel();
        $objPHPExcel->getProperties()
            ->setCreator("yksvmi")
            ->setLastModifiedBy("yksvmi")
            ->setTitle("Office 2003 XLSX Test Document")
            ->setSubject("Office 2003 XLSX Test Document")
            ->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
            ->setKeywords("office 2003 openxml php")
            ->setCategory("Test result file");
        $objActSheet = $objPHPExcel->setActiveSheetIndex(0);
        $objActSheet->setCellValue('A1','SKU');
        $objActSheet->setCellValue('B1','入库数量');
        $objActSheet->setCellValue('C1','备注');
        $objActSheet->setCellValue('D1','入库类型(中文)');
        $objActSheet->setCellValue('E1','供应商换货入库单价');
        $objActSheet->setCellValue('F1','仓库ID');
        $objActSheet->setCellValue('A2','A301');
        $objActSheet->setCellValue('B2','70');
        $objActSheet->setCellValue('C2','12342352');
        $objActSheet->setCellValue('D2','盘盈');
        $objActSheet->setCellValue('E2','10');
        $objActSheet->setCellValue('F2','5');
        $objPHPExcel->getActiveSheet()
            ->setTitle('warehouse_special');
        $objPHPExcel->setActiveSheetIndex(0);
        $day = date("m-d");
        ob_end_clean();
        header("Content-Type: application/vnd.ms-excel; charset=utf-8");
        header('Content-Disposition: attachment;filename="'.$day.'入库单.xls"');
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');
        $objWriter->save('php://output');
        exit;
    }

    /*
     * @author 俞晴
     * 处理上传的入库表格数据 进入数据库
     */
    public function action_upload_warehouse()
    {
        $content = View::factory('warehouse/warehouse_upload_result');
        $this->title = '上传结果';
        $skus        = array(); //上传的sku数组

        $warehouse_id = Kohana::$config->get('warehouse')->warehouse_id;

        //判断是否有文件提交
        if(isset($_FILES['xls']['tmp_name'])){
            set_time_limit(0);
            ini_set('memory_limit','500M');
            require_once(Kohana::find_file('vendor','PHPExcel/PHPExcel/IOFactory'));
            $xls         = $_FILES['xls']['tmp_name'];
            $objPHPExcel = PHPExcel_IOFactory::load($xls);
            $lists       = $objPHPExcel->getActiveSheet()
                ->toArray(NULL,TRUE,TRUE,TRUE);

            //去除第一行
            unset($lists[1]);

            $lists = $this->checkxls($lists);
            //当错误信息为空时 才允许提交
            if(!$lists['error']){
                unset($lists['error']);
                $places = array();
                foreach($lists as $list){
                    $skus[] = trim($list['A']);
                    $places[trim($list['A'])] = Skusystemapi::getSkuPlacesForVMI(trim($list['A']), trim($list['F']));
                }
                //获得SKU数组的中文名称
                /*$sql = DB::select('skusystem_cnname.name',DB::expr("upper(skusystem_sku_cnname.sku) as sku"))
                    ->from('skusystem_sku_cnname')
                    ->join('skusystem_cnname')
                    ->on('skusystem_sku_cnname.attr_id','=','skusystem_cnname.id')
                    ->where('sku','IN',$skus)
                    ->compile('newerp');

                $skus_names = checkstore::getDatas($sql,'name');*/
                $skus = array_map('addslashes', $skus);
                $sku_str = "'" . join("','", $skus);
                $skus_names = Skusystemapi::getMoreSkuCnname($sku_str);
                $skus_names = checkstore::array_columns($skus_names,'name');

                //获得SKU数组的储位
                /*$sql = "SELECT
                     upper(`ps`.`sku`) `sku` , `p`.`place`
                FROM `skusystem_places` `p`
                JOIN `skusystem_place_skus` `ps`
                ON `p`.`id` = `ps`.`place_id`
                WHERE `ps`.`sku` IN('".join("','",$skus)."')
                AND `ps`.`isavailable` = 1 AND p.warehouse_id = $warehouse_id";

                $places = checkstore::getDatas($sql,'place');*/

                //获得SKU数字的价格
//                $prices   = checkstore::getSkuPrice($skus);
                $prices   = Hznewerpclient::getNewPrice($skus);
                $type_arr = DB::select('value','number')
                    ->from('statusdics')
                    ->where('table','=','warehouseorders')
                    ->where('colum_name','=','type')
                    ->execute('newerp')
                    ->as_array('value','number');

                try {
                    Database::instance('newerp')->begin();
                    foreach($lists as $_list){
                        if(isset($_list['E'])&&$_list['E']>0){
                            $price = trim($_list['E']);
                        }else{
                            $price = $prices[$_list['A']];
                        }
                        $insert_arr = array(
                            'warehouse_date'=>date("Y-m-d H:i:s"),
                            'op_time'=>date("Y-m-d H:i:s"),
                            'deliveryorders_id'=>0,
                            'supplier_id'=>0,
                            'sku'=>$_list['A'],
                            'sku_name'=>mysql_escape_string($skus_names[$_list['A']]),
                            'storage_position'=>isset($places[$_list['A']]) ? $places[$_list['A']] : 0,
                            'warehouse_quantity'=>trim($_list['B']),
                            'single_price'=>$price,
                            'money'=>$price*trim($_list['B']),
                            'cost'=>$price*trim($_list['B']),
                            'remark'=>trim($_list['C']),
                            'status'=>0,
                            'type'=>$type_arr[$_list['D']],
                            'warehouse_man'=>User::active_user()->id,
                            'warehouse_id' => trim($_list['F'])
                        );
                        DB::insert('warehouseorders',array_keys($insert_arr))->values($insert_arr)->execute('newerp');
                    }
                    Database::instance('newerp')->commit();
                    $content->set('sum',count($lists));
                } catch (Exception $e) {
                    Database::instance('newerp')->rollback();
                    $content->set('errormsg','插入数据时出现错误，请查看数据是否部分提交'.$e->getMessage());
                }
            }else{
                $content->set('errormsg',$lists['error']);
            }
            $this->response->body($content);
        }
    }

    /**
     * @param $data表格数据
     * @return mixed
     * 检查表格里必须填写的几项是否准确
     */
    public static function checkxls($data)
    {
        $type_arr = DB::select('value')
            ->from('statusdics')
            ->where('table','=','warehouseorders')
            ->where('colum_name','=','type')
            ->execute('newerp')
            ->as_array(null,'value');
        $warehouse = Basic::get_warehouse_list();
        $str = '';
        foreach ($data as $k => &$_v) {
            $_str     = '';
            if (!$_v['A'] && !$_v['B'] && !$_v['C'] && !$_v['D'] && !$_v['E'] && !$_v['F']) continue;
            $_v['A'] = strtoupper(trim($_v['A']));
            if(!isset($_v['A'])||empty($_v['A'])){
                $_str .= '你输入的第'.$k."行SKU不能为空<br/>";
            }
            if(empty($_v['B'])||!preg_match('/^(\d+)?$/',$_v['B'])){
                $_str .= '你输入的第'.$k."行数量不符合规则<br/>";
            }
            if(!Deliverycheck::check_sku(strtoupper(trim($_v['A'])))){
                $_str .= '你输入的第'.$k."行SKU有误<br/>";
            }
            if(!in_array(trim($_v['D']),$type_arr)){
                $_str .= "你输入的第{$k}行入库类型有误<br/>";
            }
            if (!in_array(trim($_v['F']), array_keys($warehouse))) {
                $_str .= "你输入的第{$k}行入库ID有误<br/>";
            }
            $str .= $_str;
        }
        unset($_v);
        $data['error'] = $str;
        return $data;
    }
    
    /**
     * 获取SKU单价：优化获取供应商最新定价，如果无定价则取最新预约价
     */
    protected function getSignlePrice($skus, $supplier_id = 0) {
        $prices = array();
        if(!is_array($skus)) {
            $skus = explode(',', $skus);
        }
        if(empty($skus)) {
            return $prices;
        }
        $sku_str = "'" . implode("','", array_map('addslashes', $skus)) . "'";
        $sql = "SELECT sku,price FROM vmi_supplier_skus WHERE isavailable=1 AND sku IN ({$sku_str})";
        if($supplier_id) {
            $sql .= " AND supplier_id=" . intval($supplier_id);
        }
        $sql .= ' ORDER BY modifytime';
        $rows = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();
        if(empty($rows)) {
            return $prices;
        }
        foreach($rows as $row) {
            $prices[$row['sku']] = $row['price'];
        }
        $sku_diff = array_diff($skus, array_keys($prices));
        if(!empty($sku_diff)) {
            $sku_str = "'" . implode("','", array_map('addslashes', $sku_diff)) . "'";
            $sql = "SELECT sku,single_price FROM purchaseorder_details t1 WHERE sku IN ({$sku_str}) AND id=(
                    SELECT MAX(id) FROM purchaseorder_details t2 WHERE t1.sku=t2.sku
                )";
            $rows = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();
            if(!empty($rows)) {
                foreach($rows as $row) {
                    $prices[$row['sku']] = $row['single_price'];
                }
            }
        }
        return $prices;
    }
}
